import joblib
import pandas as pd
import random
from datetime import datetime,timedelta
import pymysql
from sshtunnel import SSHTunnelForwarder
from algorithm.city_code import depart_arrival,day

def get_decode(str,column):
    column_df = pd.read_excel(f'./encode/{column}_encoded.xlsx')
    # 将第二个Excel文件转换为一个字典，其中键和值分别是两列的数据
    map_dict = {row[column]: row[column+ '_encoded'] for index, row in column_df.iterrows()}
    return map_dict[str]

def get_random_element(column):
    column_df = pd.read_excel(f'./encode/{column}_encoded.xlsx')
    # 将第二个Excel文件转换为一个字典，其中键和值分别是两列的数据
    list = [row[column + '_encoded'] for index, row in column_df.iterrows()]
    return random.choice(list)

def predict(departureCityName,arrivalCityName,preDay):
    # 加载model
    model = joblib.load("./model/best_tree.joblib")
    df = pd.read_excel("携程航班处理数据.xlsx")
    filtered_df = df[(df['departureCityName'] == departureCityName) & (df['arrivalCityName'] == arrivalCityName)]
    # 按照airlineName, departureCityName, arrivalCityName, preDay进行分组，并计算每组的price最小值
    # min_price_indices = filtered_df.groupby(['airlineName', 'departureCityName', 'arrivalCityName', 'preDay']).price.idxmin()
    min_price_indices = filtered_df.groupby(['airlineName', 'preDay']).price.idxmin()
    min_price_indices_df = pd.DataFrame(min_price_indices).reset_index()
    # 遍历priceindex列的每个值作为行索引
    test_df = pd.DataFrame(columns=["airlineName", "departureCityName",'departureAirportName','arrivalCityName',
                  'arrivalAirportName','duration','discountRate','departureMonth','departureDay',
                  'departureHour','departureMinute','weekday','preDay'])
    airlineList = []
    preDayList = []
    predict_df = pd.DataFrame()
    for index in min_price_indices_df['price'].unique():
        # 获取对应行索引的行记录
        row = df.loc[index]
        if row.preDay > preDay:
            continue
        # 输出行记录
        #print(row)
        test_df.loc[len(test_df)] = [get_decode(row.airlineName, 'airlineName'), get_decode(departureCityName, 'departureCityName'),
                     get_decode(row.departureAirportName, 'departureAirportName'), get_decode(arrivalCityName, 'arrivalCityName'),
                     get_decode(row.arrivalAirportName, 'arrivalAirportName'), row.duration, row.discountRate,
                     row.departureMonth, row.departureDay, row.departureHour, row.departureMinute, row.weekday,
                     row.preDay]
        airlineList.append(row.airlineName)
        preDayList.append(row.preDay)
    y_test = model.predict(test_df)
    predict_df.insert(loc=0, column='airlineName', value=airlineList)
    predict_df.insert(loc=1, column='preDay', value=preDayList)
    predict_df.insert(loc=2, column='price', value=y_test)
    predict_df.to_excel(f"{departureCityName}-{arrivalCityName}价格预测.xlsx", index=False)

def loadPredict(departureCityName,arrivalCityName,preDay):
    p_df = pd.read_excel(f"{departureCityName}-{arrivalCityName}价格预测.xlsx")
    # 获取当前日期
    current_date = datetime.now().date()
    # 初始化结果字典
    x_dict = {}
    # 遍历每个airlineName
    for airline, group in p_df.groupby('airlineName'):
        # 为当前airlineName获取前十条数据
        head_group = group.head(preDay)
        # 使用apply函数和lambda函数来添加preDay值到当前日期
        dates = head_group['preDay'].apply(lambda x: (current_date + timedelta(days=x)).strftime("%Y-%m-%d")).tolist()
        # 将日期列表添加到结果字典中
        x_dict[airline] = dates
    # 对于每个组，提取preDay为0到10的price值，并转换为list
    y_dict = p_df.groupby('airlineName')['price'].agg(lambda x: x.values[:preDay].tolist()).to_dict()
    minprice_dict = p_df.groupby('airlineName')['price'].apply(lambda x: x.head(preDay).min()).to_dict()
    return x_dict,y_dict,minprice_dict

def insertPredict():
    server = SSHTunnelForwarder(
        ('123.249.93.204', 22),  # B机器的配置
        ssh_password='cxZS1234',
        ssh_username='root',
        remote_bind_address=('127.0.0.1', 3306)
    )
    server.start()
    connection = pymysql.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
                             port=server.local_bind_port,
                             user='hpzs',
                             passwd='123456',
                             db='flight')
    try:
        with connection.cursor() as cursor:
            list_info = depart_arrival()
            for tuple in list_info:
                predict(tuple[0], tuple[1], day)
                x_dict, y_dict, minprice_dict = loadPredict(tuple[0], tuple[1], day)
                print(minprice_dict)
                for key in x_dict:
                    if len(x_dict[key]) < day:
                        continue
                    insert_query = "INSERT INTO flight_predictinfordate(airline,departure_city,arrival_city,date1,date2,date3,date4,date5,date6,date7,date8,date9,date10,date11,date12,date13,date14,date15) VALUES (%s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"
                    data = (key, tuple[0], tuple[1], x_dict[key][0], x_dict[key][1],x_dict[key][2], x_dict[key][3],
                            x_dict[key][4], x_dict[key][5],x_dict[key][6],x_dict[key][7], x_dict[key][8],
                            x_dict[key][9], x_dict[key][10],x_dict[key][11],x_dict[key][12], x_dict[key][13],
                            x_dict[key][14])
                    cursor.execute(insert_query, data)

                for key in y_dict:
                    if len(y_dict[key]) < day:
                        continue
                    insert_query = "INSERT INTO flight_predictinfor(departure_city,arrival_city,minprice,price1,price2,price3,price4,price5,price6,price7,price8,price9,price10,price11,price12,price13,price14,price15,airline) VALUES (%s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s, %s,%s)"
                    data = ( tuple[0], tuple[1],
                            min(y_dict[key][0],y_dict[key][1],y_dict[key][2],y_dict[key][3],y_dict[key][4],y_dict[key][5],y_dict[key][6],y_dict[key][7],y_dict[key][8],y_dict[key][9],y_dict[key][10],y_dict[key][11],y_dict[key][12],y_dict[key][13],y_dict[key][14]),
                            y_dict[key][0], y_dict[key][1],y_dict[key][2], y_dict[key][3],y_dict[key][4], y_dict[key][5],y_dict[key][6],
                            y_dict[key][7], y_dict[key][8],y_dict[key][9], y_dict[key][10],y_dict[key][11],
                            y_dict[key][12], y_dict[key][13],y_dict[key][14],key)
                    cursor.execute(insert_query, data)
            # 提交事务
            connection.commit()
    finally:
        connection.close()
    server.close()